package com.braveo.dict;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FilenameFilter;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class BraveDictDbAdapter {
	public static final String DB_DIRECTORY = "/sdcard/brave-dict";
	@SuppressWarnings("unused")
	private static int          DB_VERSION = 1;
	private static final String TABLE_NAME = "dict";
	private static final Pattern DICT_NAME_PATTERN = Pattern.compile("BraveDict_(.+)\\.db");	
	
	private String db_name = null;	//Ask the user to choose a dictionary when program starts up
	private SQLiteDatabase db;

	private static BraveDictDbAdapter instance = null;
	
	@SuppressWarnings("unused")
	private static final String[] DB_CREATE = {
		"CREATE TABLE dict ( _id INTEGER PRIMARY KEY AUTOINCREMENT, Word TEXT NOT NULL, EXPLANATION TEXT NOT NULL );",
		"CREATE INDEX word_index ON dict (Word);",
		"CREATE TABLE android_metadata (locale TEXT);",
		"INSERT INTO android_metadata(locale) VALUES('en_US');",
		"CREATE TABLE image ( _id INTEGER PRIMARY KEY AUTOINCREMENT, " +
				"wordId INTEGER NOT NULL, " +
				"imageTitle TEXT, " +
				"originalFileName TEXT NOT NULL, " +
				"mimeType TEXT NOT NULL, " +
				"imageData BLOG);",
		"CREATE INDEX image_word_index ON image (wordId);",
	};
	
	@SuppressWarnings("unused")
	private static final String[] DB_SAMPLE_DATA = {
		"INSERT INTO dict(Word, Explanation) VALUES('hello', 'This is a hello for test');",
		"INSERT INTO dict(Word, Explanation) VALUES('world', 'This is a world for test');",
	};
	


	/**
	 * Do nothing in the contructor
	 */
	private BraveDictDbAdapter(Context ctx){
		//this.ctx = ctx;
	}
	
	public static synchronized BraveDictDbAdapter getInstance(Context ctx){
		if(instance==null)
			instance = new BraveDictDbAdapter(ctx);
		return instance;
	}
	
	/**
	 * 
	 * @param name Database file name. Not the short name.
	 * @throws FileNotFoundException
	 */
	public void setDbName(String name) throws FileNotFoundException{
		String oldName = this.db_name;
		
		for(OnDictChanged odc : onDictChangedListeners){
			odc.beforeChange(this.db_name, name);
		}
		
		File file = new File(DB_DIRECTORY + "/" + name); 
		if(!file.exists()){
			throw new FileNotFoundException(file.getAbsolutePath());
		}
		
		if(this.db_name == null || !this.db_name.equals(name)){
			this.db_name = name;
			close();
			open();
		}

		for(OnDictChanged odc : onDictChangedListeners){
			odc.afterChange(oldName, name);
		}		
	}

	public synchronized boolean open() throws SQLException {
		if(db==null){
			db = SQLiteDatabase.openDatabase(DB_DIRECTORY + "/" + db_name, null, SQLiteDatabase.OPEN_READONLY);
		}
		return true;
	}
	
	public synchronized void close() throws SQLException {
		if(db!=null)
			db.close();
		db = null;
	}
	
	public static List<String> listDictDbs(){
		File dir = new File(DB_DIRECTORY);
		
		if(!dir.exists() || !dir.isDirectory())
			return null;
		
		File[] files = dir.listFiles(new FilenameFilter(){
			public boolean accept(File dir, String name){
				Matcher m = DICT_NAME_PATTERN.matcher(name);
				if(!m.matches())
					return false;
				File f = new File(dir.getAbsolutePath() + "/" + name);
				if(f.isDirectory())
					return false;
				return true;
			}
		});
		
		List<String> ret = new LinkedList<String>();
		for(int k=0; k<files.length; k++){
			String name = files[k].getName();
			Matcher m = DICT_NAME_PATTERN.matcher(name);
			if(m.matches())
				ret.add(m.group(1));
		}
		
		return ret;
	}
	
	public Cursor getAllWords(){
		return db.query(TABLE_NAME, new String[] { "_id", "Word"}, null, null, null, null, "Word");
	}

	public Cursor getFewWords(){
		return db.query(TABLE_NAME, new String[] { "_id", "Word"}, null, null, null, null, "Word", "10");
	}
	
	public Cursor getExplanation(String word){
		return db.query(TABLE_NAME, new String[] { "Word", "Explanation"}, "Word=?", new String[] {word}, null, null, null, "1");
	}
	
	public Cursor getWordByConstraint(CharSequence constraint) {
		//if(constraint == null || constraint.length()<=2)
		//	return db.rawQuery("SELECT _id, '' FROM dict LIMIT 0", null);
			
		//Log.w("CC", "constrain = " + constraint);
		
		StringBuilder buffer = null;
		String [] args = null;
		if(constraint != null){
			buffer = new StringBuilder();
			//buffer.append("Word GLOB ? ");
			//args = new String[] { constraint.toString() + "*" };
			buffer.append("word > ? ");
			args = new String[] { constraint.toString() };
		}
		
		/*
		return db.query("dict", 
				new String [] { "_id", "Word" }, 
				buffer==null ? null : buffer.toString(), args, null, null,
				"Word", "10");
				*/
		String condit = "";
		if(buffer!=null)
			condit = " WHERE " + buffer.toString();
		
		String sql = "SELECT _id, Word FROM dict " + condit + " ORDER BY Word LIMIT 20";
		/*int k;
		String logMsg = sql;
		for(k=0; args!=null && k<args.length; k++){
			logMsg = logMsg + " " + args[k];
		}
		Log.w("BDD", logMsg);*/
		return db.rawQuery(sql, args);

	}
	
	public int getWordId(String word) {
		if(word == null || word.length() == 0)
			return -1;
		
		String sql = "SELECT _id FROM dict WHERE Word = ?";
		Cursor c = db.rawQuery(sql, new String[] { word });
		int result = -1;
		
		if(c.moveToFirst())
			result = c.getInt(0);
		c.close();
		
		return result;
	}
	
	public String getNextWord(String word){
		if(word==null || word.length()==0)
			return null;
		
		String sql = "SELECT Word FROM dict WHERE Word > ? ORDER BY Word LIMIT 1;";
		Cursor c = db.rawQuery(sql, new String[] { word });
		if(!c.moveToFirst()){
			c.close();
			return null;
		}
		String w = c.getString(0);
		c.close();
		return w;
	}
	
	public List<String> getNextWords(String word, int num){
		List<String> result = new LinkedList<String>();
		if(word==null || word.length() == 0)
			return result;
		
		String sql = "SELECT Word FROM dict WHERE Word > ? ORDER BY Word LIMIT " + num;
		Cursor c = db.rawQuery(sql, new String[] { word });
		
		if(c.moveToFirst()){
			do{
				result.add(c.getString(0));
			}while(c.moveToNext());
		}
		
		c.close();
		return result;
		
	}
	
	public List<String> getCaseNextWords(String word, int num){
		List<String> result = new LinkedList<String>();
		if(word==null || word.length() == 0)
			return result;
		
		String sql = "SELECT Word FROM dict WHERE Word LIKE ? ORDER BY Word LIMIT " + num;
		Cursor c = db.rawQuery(sql, new String[] { word + "%" });
		
		if(c.moveToFirst()){
			do{
				result.add(c.getString(0));
			}while(c.moveToNext());
		}
		
		c.close();
		return result;
		
	}	

	public List<String> getPrevWords(String word, int num){
		List<String> result = new LinkedList<String>();
		if(word==null || word.length() == 0)
			return result;
		
		String sql = "SELECT Word FROM dict WHERE Word < ?  ORDER BY Word DESC LIMIT " + num;
		Cursor c = db.rawQuery(sql, new String[] { word });
		
		if(c.moveToFirst()){
			do{
				result.add(c.getString(0));
			}while(c.moveToNext());
		}
		
		c.close();
		return result;
		
	}
	
	public String getPrevWord(String word){
		if(word==null || word.length()==0)
			return null;
		
		String sql = "SELECT Word FROM dict WHERE Word < ? ORDER BY Word DESC LIMIT 1;";
		Cursor c = db.rawQuery(sql, new String[] { word });
		if(!c.moveToFirst()){
			c.close();
			return null;
		}
		String w = c.getString(0);
		c.close();
		return w;
	}

	public String getDatabaseName() {
		return this.db_name;
	}
	
	
	public String getDatabaseShortName() {
		Matcher m = DICT_NAME_PATTERN.matcher(db_name);
		if(m.matches())
			return m.group(1);
		return db_name;
	}
	
	public static String dictShortNameToFullName(String shortName){
		return "BraveDict_" + shortName + ".db";
	}
	
	public static boolean dictExists(String dbName){
		File file = new File(DB_DIRECTORY + "/" + dbName);
		return file.exists();
	}
	
	public static interface OnDictChanged {
		public Object getOwner();
		public void beforeChange(String oldDbName, String newDbName);
		public void afterChange(String oldDbName, String newDbName);
	}
	
	private final List<OnDictChanged> onDictChangedListeners = new LinkedList<OnDictChanged>();
	
	public void addListener(OnDictChanged listener){
		for(OnDictChanged odc : onDictChangedListeners){
			if(odc.getOwner() == listener.getOwner())
				onDictChangedListeners.remove(odc);
		}
		onDictChangedListeners.add(listener);
	}
	
	public void removeListener(Object owner){
		for(OnDictChanged odc : onDictChangedListeners){
			if(odc.getOwner() == owner){
				onDictChangedListeners.remove(odc);
			}
		}
	}
	
	private ImageDataLoader fakeImageDataLoader = new ImageDataLoader() {
		
		public BraveImageObject loadImage(int imageId) {
			return new BraveImageObject.Builder()
				.setImageId(imageId).setWordId(getWordIdByImageId(imageId))
				.setImageTitle("Not available").setMimeType("image/png")
				.setOriginalFileName("invalid.png").setImageData(new byte[0]).build();
		}
		
		public int getWordIdByImageId(int imageId) {
			return 1001;
		}
		
		public List<Integer> getImageIdListByWordId(int wordId) {
			return Collections.<Integer>emptyList();
		}
	};
	
	private ImageDataLoader realImageDataLoader = new ImageDataLoader() {
		
		public BraveImageObject loadImage(int imageId) {
			String sql = "SELECT _id, wordId, imageTitle, mimeType, originalFileName, imageData " +
					"FROM " + IMAGE_TABLE_NAME + " WHERE _id=?";
			
			Cursor c = db.rawQuery(sql, new String[] { String.valueOf(imageId) });
			
			BraveImageObject image = null;
			
			if(c.moveToFirst()) {
				int col = 1;
				image = new BraveImageObject.Builder()
					.setImageId(imageId)
					.setWordId(c.getInt(col++))
					.setImageTitle(c.getString(col++))
					.setMimeType(c.getString(col++))
					.setOriginalFileName(c.getString(col++))
					.setImageData(c.getBlob(col)).build();
			}
			c.close();
			return image;
		}
		
		public int getWordIdByImageId(int imageId) {
			String sql = "SELECT _id, wordId FROM " + IMAGE_TABLE_NAME + " WHERE _id = ?";
			
			Cursor c = db.rawQuery(sql, new String[] { String.valueOf(imageId) });
			int result = -1;
			if(c.moveToFirst())
				result = c.getInt(0);
			
			c.close();
			return result;
		}
		
		public List<Integer> getImageIdListByWordId(int wordId) {
			String sql = "SELECT _id FROM " + IMAGE_TABLE_NAME + " WHERE wordId = ?";
			
			ArrayList<Integer> list = new ArrayList<Integer>();			
			Cursor c = db.rawQuery(sql, new String[] { String.valueOf( wordId ) });

			if(c.moveToFirst()) {
				do {
					list.add(c.getInt(0));
				}while(c.moveToNext());
			}
			
			c.close();
			return list;
		}
	};
	
	Boolean imageDataReady = null;
	static final String IMAGE_TABLE_NAME = "image";
	
	private synchronized boolean checkImageDataReady() {
		if(db == null)
			return false;
	
		if(imageDataReady != null)
			return imageDataReady.booleanValue();
		
		String sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?";
		
		Cursor c = db.rawQuery(sql, new String[] { IMAGE_TABLE_NAME });
		if(!c.moveToFirst())
			imageDataReady = Boolean.valueOf(false);
		else
			imageDataReady = Boolean.valueOf(true);

		c.close();

		return imageDataReady.booleanValue();
	}
	
	public ImageDataLoader createImageDataLoader() {
		if(!checkImageDataReady()) {
			return fakeImageDataLoader;
		}
		
		return realImageDataLoader;
	}
}
